package com.eryos.android.cigarettecounter.database;

/**
 * Project :  CigaretteCounter
 * Author :   Olivier Combe 
 * URL :      http://eryos.fr/CigaretteCounter/
 *
 * Copyright (C) 2011 - Olivier Combe 
 * 
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 * 
 * http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 *
 */

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.TimeZone;

import android.content.Context;
import android.database.Cursor;
import android.database.CursorIndexOutOfBoundsException;
import android.util.Log;

import com.eryos.android.cigarettecounter.beans.SmokeAverage;
import com.eryos.android.cigarettecounter.beans.SmokeCounter;
import com.eryos.android.cigarettecounter.beans.SmokeTime;


public class CigaretteDatabase extends DBAdapter {

	public static final int NUMBER_OF_HISTORY = 6;	
	public static final int ROUNDING = 2;
	
	private static final String DATE_FORMAT = "%d/%m/%Y"; 
	private static final String WEEK_FORMAT = "%W"; 
	private static final String MONTH_FORMAT = "%m"; 
	
	private static final String NOW = "datetime("+System.currentTimeMillis()/1000+", 'unixepoch', 'localtime')";
	
	private static final String THIS_DAY = "strftime('"+DATE_FORMAT+"', "+NOW+")";
	private static final String THIS_WEEK = "strftime('"+WEEK_FORMAT+"', "+NOW+")";
	private static final String THIS_MONTH = "strftime('"+MONTH_FORMAT+"', "+NOW+")";
	
	private static final String SIX_WEEKS_AGO = "strftime('%Y-%W', "+NOW+", '-"+(NUMBER_OF_HISTORY*7)+" days')";
	private static final String SIX_MONTHS_AGO = "strftime('%Y-%m', "+NOW+", '-"+NUMBER_OF_HISTORY+" months')";

	private static final String DB_WEEK = "strftime('"+WEEK_FORMAT+"', "+KEY_SMOKE_TIME+")";
	private static final String DB_MONTH = "strftime('"+MONTH_FORMAT+"', "+KEY_SMOKE_TIME+")";
	
	private static final String CSV_SEPARATOR = ";";
	
	/**
	 * Create a new CigaretteDatabase Object link to the context in argument
	 * @param ctx Application context
	 */
	public CigaretteDatabase(Context ctx) {
		super(ctx);
	}

	/*****************************************************************************/
	/**
	 * Return the result of the division between SUM and NB
	 */
	public double getAverage(double _sum, double _nb){

		try {
			if ( _nb != 0 ){
				BigDecimal sum = new BigDecimal(_sum).setScale(2);
				BigDecimal nb  = new BigDecimal(_nb).setScale(2);

				return sum.divide(nb, ROUNDING, RoundingMode.DOWN).doubleValue();
			}else
				return 0;
		} catch (Exception e) {
			Log.e(this.getClass().getSimpleName(), "Compute Average", e);
			return -1;
		}		
	}

	/**
	 * Parse an SQL cursor into a ArrayList of SmokeAverage(Label/Double) 
	 */
	public ArrayList<SmokeAverage> parseCursorAsSmokeAverage( Cursor mCursor ){

		ArrayList<SmokeAverage> res = new ArrayList<SmokeAverage>();

		if (mCursor != null){
			mCursor.moveToFirst();
			for(int i=0; i < mCursor.getCount(); i++) {			
				res.add(new SmokeAverage(mCursor.getString(0), mCursor.getDouble(3) ));
				mCursor.moveToNext();
			}
		}

		return res;
	}

	/**
	 * Parse an SQL cursor into a ArrayList of SmokeCount(Label/Int) 
	 */
	public ArrayList<SmokeCounter> parseCursorAsSmokeCounter( Cursor mCursor ){

		ArrayList<SmokeCounter> res = new ArrayList<SmokeCounter>();

		if (mCursor != null){
			mCursor.moveToFirst();
			for(int i=0; i < mCursor.getCount(); i++) {
				SmokeCounter smoke = new SmokeCounter(mCursor.getString(0),mCursor.getInt(1));

				try {
						Calendar c = Calendar.getInstance();
						c.set(Calendar.DAY_OF_MONTH, mCursor.getInt(2));
						c.set(Calendar.MONTH, mCursor.getInt(3));
						c.set(Calendar.YEAR, mCursor.getInt(4));
						smoke.setDate(c);
				} catch (Exception e) {
					Log.e(this.getClass().getSimpleName(), "Parse as Smoke Counter", e);					
				}

				res.add(smoke);
				mCursor.moveToNext();
			}
		}

		return res;
	}

	/*****************************************************************************/

	/**
	 * Return the number of cigarettes smoked this day
	 */
	public int getDailyCount(){
		Log.i(getClass().getSimpleName(), "get Daily Count");
		String sqlQuery = 	
			"SELECT   strftime('"+DATE_FORMAT+"', smoke_time), count(1) "+
			"FROM     "+ DATABASE_TABLE +" "+
			"WHERE    strftime('"+DATE_FORMAT+"', smoke_time) = "+THIS_DAY+" "+
			"GROUP BY strftime('"+DATE_FORMAT+"', smoke_time) ;";

		int res = 0;

		Cursor mCursor = db.rawQuery(sqlQuery, null);

		if (mCursor != null && mCursor.getCount() > 0) {
			mCursor.moveToFirst();
			try {
				res = mCursor.getInt(1);
			} catch (CursorIndexOutOfBoundsException e) {
				Log.w(getClass().getSimpleName(), "get Daily Count", e);
			}
		}

		return res;
	}

	/**
	 * Return the number of cigarettes smoked this week
	 */
	public int getWeeklyCount(){
		Log.i(this.getClass().getSimpleName(), "get Weekly Count");
		String sqlQuery = 
			"SELECT   count(1) Nb "+
			"FROM     "+ DATABASE_TABLE +" "+
			"WHERE    "+DB_WEEK+" = "+THIS_WEEK+"; ";

		int res = -1;

		Cursor mCursor = db.rawQuery(sqlQuery, null);

		if (mCursor != null) {
			mCursor.moveToFirst();
			res = mCursor.getInt(0);
		}

		return res;
	}
	
	/**
	 * Return the number of cigarettes smoked this week
	 */
	public double getWeeklyCost(){
		Log.i(this.getClass().getSimpleName(), "get Weekly Cost");
		String sqlQuery = 
			"SELECT   round(sum(ifnull(smoke_valid,0)), "+ROUNDING+") Nb "+
			"FROM     "+ DATABASE_TABLE +" "+
			"WHERE    strftime('%W', smoke_time) = "+THIS_WEEK+"; ";

		double res = -1;

		Cursor mCursor = db.rawQuery(sqlQuery, null);

		if (mCursor != null) {
			mCursor.moveToFirst();
			res = mCursor.getDouble(0);
		}

		return res;
	}

	/**
	 * Return the average number of cigarettes smoked by day this week
	 */
	public double getWeeklyAverage(){
		Log.i(this.getClass().getSimpleName(), "get Weekly Average");
		try {
			String sqlQuery = 
				"SELECT sum(nb), count(nb), round(avg(nb), "+ROUNDING+") "+
				"FROM  "+
				"( "+
				"    SELECT   strftime('%d/%m/%Y', smoke_time) Day, count(1) Nb  "+
				"    FROM     "+ DATABASE_TABLE +" "+
				"    WHERE    strftime('%W', smoke_time) = "+THIS_WEEK+" "+
				"    GROUP BY strftime('%d/%m/%Y', smoke_time) "+
				"    ORDER BY strftime('%d/%m/%Y', smoke_time) "+
				");";

			double res = -1;

			Cursor mCursor = db.rawQuery(sqlQuery, null);

			if (mCursor != null) {
				mCursor.moveToFirst();
				res =  mCursor.getDouble(2);getAverage( mCursor.getDouble(0), mCursor.getDouble(1) );
			}

			return res;
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		}
	}

	/**
	 * Return the number of cigarettes smoked each day of the week
	 */
	public ArrayList<SmokeCounter> getWeeklyDetails(){
		Log.i(this.getClass().getSimpleName(), "get Weekly Details");
		String sqlQuery =
			//"SELECT   strftime('%w', smoke_time) Day, count(1) Nb  " +
			"SELECT   strftime('%w', smoke_time) Day, count(1) Nb, strftime('%d', smoke_time) Day, strftime('%m', smoke_time) Month, strftime('%Y', smoke_time) Year  "+
			"FROM     "+ DATABASE_TABLE +" " +
			"WHERE    strftime('%W', smoke_time) = "+THIS_WEEK+" " +
			"GROUP BY strftime('%w', smoke_time) " +
			"ORDER BY strftime('%Y-%m-%d', smoke_time); ";

		Cursor mCursor = db.rawQuery(sqlQuery, null);

		return parseCursorAsSmokeCounter(mCursor);
	}

	/**
	 * Return the average number of cigarettes smoked by day for the last 6 weeks
	 */
	public ArrayList<SmokeAverage> getLastWeeksAverage(){
		Log.i(this.getClass().getSimpleName(), "get Last 6 Weekly History");
		String sqlQuery = 
			"SELECT Week, sum(nb), count(nb), round(avg(nb), "+ROUNDING+") "+
			"FROM  "+
			"( "+
			"    SELECT   strftime('%W', smoke_time) Week, strftime('%Y-%m-%d', smoke_time) Day, count(1) Nb  "+
			"    FROM     "+ DATABASE_TABLE +" "+
			"    WHERE    strftime('%Y-%W', smoke_time) > "+SIX_WEEKS_AGO+" "+
			"    GROUP BY strftime('%W', smoke_time), strftime('%d/%m/%Y', smoke_time) "+ 
			") "+
			"GROUP BY Week " + 
			"ORDER BY Day; ";

		Cursor mCursor = db.rawQuery(sqlQuery, null);

		return parseCursorAsSmokeAverage(mCursor);
	}

	/**
	 * Return the number of cigarettes smoked this month
	 */
	public int getMonthlyCount(){
		Log.i(this.getClass().getSimpleName(), "get Monthly Count");
		String sqlQuery = 
			"SELECT   count(1) Nb "+ 
			"FROM     "+ DATABASE_TABLE +" "+
			"WHERE    strftime('%m', smoke_time) = "+THIS_MONTH+"; ";

		int res = -1;

		Cursor mCursor = db.rawQuery(sqlQuery, null);

		if (mCursor != null) {
			mCursor.moveToFirst();
			res = mCursor.getInt(0);
		}

		return res;
	}
	
	/**
	 * Return the number of cigarettes smoked this month
	 */
	public double getMonthlyCost(){
		Log.i(this.getClass().getSimpleName(), "get Monthly Cost");
		String sqlQuery = 
			"SELECT   round(sum(ifnull(smoke_valid,0)), "+ROUNDING+") Nb "+
			"FROM     "+ DATABASE_TABLE +" "+
			"WHERE    strftime('%m', smoke_time) = "+THIS_MONTH+"; ";

		double res = -1;

		Cursor mCursor = db.rawQuery(sqlQuery, null);

		if (mCursor != null) {
			mCursor.moveToFirst();
			res = mCursor.getDouble(0);
		}

		return res;
	}

	/**
	 * Return the average number of cigarettes smoked by day this month
	 */
	public double getMonthlyAverage(){		
		Log.i(this.getClass().getSimpleName(), "get Monthly Average");
		try {
			String sqlQuery = 
				"SELECT sum(nb), count(nb), round(avg(nb), "+ROUNDING+") "+
				"FROM  "+
				"( "+
				"    SELECT   strftime('%d/%m/%Y', smoke_time) Day, count(1) Nb  "+
				"    FROM     "+ DATABASE_TABLE +" "+
				"    WHERE    strftime('%m', smoke_time) = "+THIS_MONTH+" "+
				"    GROUP BY strftime('%d/%m/%Y', smoke_time) "+
				"    ORDER BY strftime('%d/%m/%Y', smoke_time) "+
				");";

			double res = -1;

			Cursor mCursor = db.rawQuery(sqlQuery, null);

			if (mCursor != null) {
				mCursor.moveToFirst();
				res = mCursor.getDouble(2);//getAverage( mCursor.getDouble(0), mCursor.getDouble(1) );
			}

			return res;
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		}
	}

	/**
	 * Return the number of cigarettes smoked by day for this month
	 */
	public ArrayList<SmokeCounter> getMonthlyDetails(){
		Log.i(this.getClass().getSimpleName(), "get Monthly Details");
		try {
			String sqlQuery = 
				"SELECT   strftime('%d/%m/%Y', smoke_time) Day, count(1) Nb, strftime('%d', smoke_time) Day, strftime('%m', smoke_time) Month, strftime('%Y', smoke_time) Year  "+ 
				"FROM     "+ DATABASE_TABLE +" "+ 
				"WHERE    strftime('%m', smoke_time) = "+THIS_MONTH+" "+ 
				"GROUP BY strftime('%d/%m/%Y', smoke_time) "+ 
				"ORDER BY strftime('%d/%m/%Y', smoke_time); ";

			Cursor mCursor = db.rawQuery(sqlQuery, null);

			return parseCursorAsSmokeCounter(mCursor);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * Return the average number of cigarettes smoked by day for the last 6 month
	 */
	public ArrayList<SmokeAverage> getLastMonthsAverage(){
		Log.i(this.getClass().getSimpleName(), "get Last 6 Months History");
		try {
			String sqlQuery = 
				"SELECT Month, sum(nb), count(nb), round(avg(nb), "+ROUNDING+") "+
				"FROM  "+
				"( "+
				"    SELECT   strftime('%m', smoke_time) Month, strftime('%Y-%m-%d', smoke_time) Day, count(1) Nb  "+
				"    FROM     "+ DATABASE_TABLE +" "+
				"    WHERE    strftime('%Y-%m', smoke_time) > "+SIX_MONTHS_AGO+" "+
				"    GROUP BY strftime('%m', smoke_time), strftime('%d/%m/%Y', smoke_time) ORDER BY smoke_time"+
				") "+
				"GROUP BY Month " +
				"ORDER BY Day; ";

			Cursor mCursor = db.rawQuery(sqlQuery, null);

			return parseCursorAsSmokeAverage(mCursor);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}	

	/**
	 * Return the time since the last cigarette
	 */
	public SmokeTime getDelaySinceLast(){
		Log.i(getClass().getSimpleName(), "get Delay Since Last Cigarette");
		try {
			SmokeTime res = new SmokeTime();

			String sqlQuery = 
				//"SELECT strftime('%s', "+NOW+"),  strftime('%s', MAX(datetime(strftime('%s', smoke_time), 'unixepoch', 'localtime'))) "+
				"SELECT strftime('%s', MAX(datetime(strftime('%s', smoke_time), 'unixepoch', 'localtime'))) "+
				"FROM "+ DATABASE_TABLE +";";

			Cursor mCursor = db.rawQuery(sqlQuery, null);

			if (mCursor != null) {
				mCursor.moveToFirst();

				String max = mCursor.getString(0)+"000";
				
				Calendar cNow = Calendar.getInstance();
				long lNow = System.currentTimeMillis();
				Calendar cMax = Calendar.getInstance();
				cNow.setTimeInMillis(lNow);
				cMax.setTimeInMillis(new Long(max));
				if ( new Long(max) <= lNow ){
					Log.d(getClass().getSimpleName(), "Last : "+max+" "+cMax.getTime().toLocaleString());
					Log.d(getClass().getSimpleName(), "Now : "+lNow+" "+cNow.getTime().toLocaleString());

					Calendar cal = Calendar.getInstance();
					cal.setTimeInMillis((lNow - new Long(max)));

					res.setHour(cal.get(Calendar.HOUR_OF_DAY));
					res.setMinute(cal.get(Calendar.MINUTE));
					res.setSeconds(cal.get(Calendar.SECOND));
					res.genTotalMinutes();
				}else{
					Log.d(getClass().getSimpleName(), "Max is greater than Now !!");
					res.setHour(0);
					res.setMinute(0);
					res.setSeconds(0);
					res.genTotalMinutes();
				}
			}

			return res;
		} catch (NumberFormatException e) {
			Log.e(getClass().getSimpleName(), "get Delay Since Last", e);
			return new SmokeTime();
		}
	}

	/**
	 * Return the average time between 2 cigarettes
	 */
	public SmokeTime getAverageDelay(){
		Log.i(this.getClass().getSimpleName(), "get Average Delay between 2 Cigarettes");
		return new SmokeTime();
		/*
		try {
			SmokeTime res = new SmokeTime();

			String sqlQuery = 
			"SELECT ROUND(AVG(cur-last), 2) as AVG_DELAY " +
			"FROM  " +
			"(   " +
			"		SELECT  "+KEY_SMOKE_TIME+"  AS cur, " +
			"		(  " +
			"				SELECT MAX("+KEY_SMOKE_TIME+") " +
			"				FROM "+ DATABASE_TABLE +" " +
			"				WHERE "+KEY_SMOKE_TIME+" < tcur."+KEY_SMOKE_TIME+" " +
			"				AND strftime('"+DATE_FORMAT+"', "+KEY_SMOKE_TIME+") = strftime('"+DATE_FORMAT+"', tcur."+KEY_SMOKE_TIME+") " +
			"		) AS last " +
			"		FROM "+ DATABASE_TABLE +" tcur " +
			"		WHERE "+DB_MONTH+" >= "+THIS_MONTH+" " +
			") " +
			"WHERE last IS NOT NULL;"; 
			System.out.println(sqlQuery);

//				"SELECT AVG(diff), strftime(AVG(diff), 'unixepoch', 'localtime') "+
//				"FROM ( "+
//				"	    SELECT ( smoke_time - ( SELECT MAX(smoke_time) FROM "+ DATABASE_TABLE +" WHERE smoke_time < t1.smoke_time ) ) as diff  "+
//				"	    FROM "+ DATABASE_TABLE +" t1  "+
//				"	    WHERE strftime('%m', smoke_time) = "+THIS_MONTH+"  "+
//				"	    ORDER BY smoke_time "+ 
//				");";

			Cursor mCursor = db.rawQuery(sqlQuery, null);

			if (mCursor != null) {
				mCursor.moveToFirst();
				System.out.println("Delay Double: "+mCursor.getDouble(0));
				res.setTotalTime(mCursor.getDouble(0));
				res.parse();
			}

			//return res;
			return new SmokeTime();
		} catch (Exception e) {
			Log.e(this.getClass().getSimpleName(), "get Average Delay", e);
			return new SmokeTime();
		}*/
	}

	/************************************************************************************/

	/**
	 * 
	 * @param _day
	 * @param _month
	 * @param _year
	 */
	public void invalidDate(int _day, int _month, int _year) {
		Log.i(this.getClass().getSimpleName(), "Invalide a date");
		String day = String.valueOf(_day);
		String month = String.valueOf(_month);
		String year = String.valueOf(_year);

		if ( day.length() == 1 )
			day = "0"+day;

		if ( month.length() == 1 )
			month = "0"+month;

		db.execSQL("DELETE FROM "+DATABASE_TABLE+" WHERE strftime('%Y-%m-%d', smoke_time) = strftime('%Y-%m-%d', '"+year+"-"+month+"-"+day+"');");       

	}

	/**
	 * 
	 * @param price
	 */
	public void insertRandomData( float price){
		Log.i(this.getClass().getSimpleName(), "Insert Random Data");

		Calendar yesterday = Calendar.getInstance(); 
		yesterday.add(Calendar.DAY_OF_MONTH, -1); // Yesterday
		Calendar c = Calendar.getInstance(); // Today
		// MONTHS 
		for ( int i = -5 ; i <= 0 ; i ++ ){

			c = Calendar.getInstance(); // Today 
			c.setTimeZone(TimeZone.getDefault());
			c.add(Calendar.MONTH, i);
			int year = c.get(Calendar.YEAR); 
			int month = c.get(Calendar.MONTH)+1;
			int lastDayOfMonth = getLastDayOfMonth(c.get(Calendar.MONTH));
			
			if ( month-1 == yesterday.get(Calendar.MONTH) )
				lastDayOfMonth = yesterday.get(Calendar.DAY_OF_MONTH);

			// DAYS 
			for ( int day = 1; day <= lastDayOfMonth ; day++ ){ // for each day of the month

				int nbInsertForCurrentDay = ((int) (Math.random()*10))+5; // between 5 and 15
				c.set(Calendar.DAY_OF_MONTH, day); 

				// OCCURENCY 
				for ( int x = 0 ; x < nbInsertForCurrentDay ; x++ ){
					int hour = ((int) (Math.random()*17))+6; // between 7 and 23 
					int minute = (int) (Math.random()*59); // between 1 and 59 
					int second = (int) (Math.random()*59); // between 1 and 59 
					int milisec = (int) (Math.random()*99); // between 1 and 99 // INSERT
					
					c.set(Calendar.HOUR_OF_DAY, hour);
					c.set(Calendar.MINUTE, minute);
					c.set(Calendar.SECOND, second);
					c.set(Calendar.MILLISECOND, milisec);
					
					Log.d(getClass().getSimpleName(), "Insert day : "+c.getTime().toLocaleString());
					
					long timestamp = c.getTimeInMillis()/1000;
					if ( price < 0 )
						price = 0;

					// TODO
					db.execSQL("INSERT INTO smoke_table ( smoke_time, smoke_reason, smoke_valid ) VALUES ( datetime("+timestamp+", 'unixepoch', 'localtime'), 'Random Data', "+price+");");
					//db.execSQL("INSERT INTO smoke_table ( smoke_time, smoke_reason, smoke_valid ) VALUES ('"+year+"-"+sMonth+"-"+sDay+" "+sHour+":"+sMinute+":"+sSecond+"', 'Random', 'Y');");
				}
			}
		}

	}

	/**
	 * 
	 * @param month
	 * @return
	 */
	public static int getLastDayOfMonth( int month ){

		Calendar c = Calendar.getInstance(); 
		c.set(Calendar.MONTH, month+1); 
		c.set(Calendar.DAY_OF_MONTH, 1); 
		c.add(Calendar.DAY_OF_MONTH, -1);

		return c.get(Calendar.DAY_OF_MONTH);
	}
	
	/**
	 * 
	 * @return
	 */
	public ArrayList<String> exportData(){
		Log.i(getClass().getSimpleName(), "Export data");
		
		ArrayList<String> data = new ArrayList<String>();
		
		data.add("Smoke Time"+CSV_SEPARATOR+"Smoke Reason"+CSV_SEPARATOR+"Smoke Price");

		try {
			String sqlQuery = 
				"SELECT strftime('%Y-%m-%d %H:%M:%S', "+KEY_SMOKE_TIME+") , "+KEY_SMOKE_REASON+", "+KEY_SMOKE_PRICE+" "+
				"FROM  "+ DATABASE_TABLE +"; ";

			Cursor mCursor = db.rawQuery(sqlQuery, null);

			if ( mCursor != null ){
				mCursor.moveToFirst();
				
				while (mCursor != null && ! mCursor.isAfterLast()) {
					String line = mCursor.getString(0)+ CSV_SEPARATOR +mCursor.getString(1)+ CSV_SEPARATOR +mCursor.getFloat(2);
					data.add(line);
					mCursor.moveToNext();
				}				
			}

		} catch (Exception e) {
			Log.e(this.getClass().getSimpleName(), "Export Data", e);
		}
		
		return data;
	}

	/**
	 * Method used to store in DB all data contained in the ArrayList in param
	 * @param data : List of data in CSV format
	 */
	public void importData(ArrayList<String> data){
		Log.i(this.getClass().getSimpleName(), "Import Data");
		for ( String line : data){

			String[] tmpValues = line.split(CSV_SEPARATOR);
			
			String sqlQuery = "INSERT INTO "+ DATABASE_TABLE +" () VALUES (null, '"+tmpValues[0]+"', '"+tmpValues[1]+"', '"+tmpValues[2]+"');";
	    	db.execSQL(sqlQuery);

		}
	}

}